﻿unit NewPrincipFrm;

interface

uses

  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, dxSkinsCore, dxSkinsdxBarPainter, cxGraphics, cxControls, cxLookAndFeels,
  cxLookAndFeelPainters, dxSkinsdxStatusBarPainter, cxStyles,
  FIBQuery, pFIBCacheQueries,
  cxGridExportLink, DateUtils,
  dxRibbonForm,
  dxSkinscxPCPainter, cxCustomData, cxFilter, cxData, cxDataStorage, cxEdit, DB,
  cxDBData, cxContainer, ComCtrls, ToolWin, GridsEh, DBGridEh, cxLabel,
  cxDropDownEdit, cxLookupEdit, cxDBLookupEdit, cxDBLookupComboBox, cxTextEdit,
  cxMaskEdit, cxCalendar, StdCtrls, Mask, JvExMask, JvSpin, JvExStdCtrls,
  JvCombobox, JvExControls, JvXPCore, JvXPBar, cxGridLevel, cxClasses,
  cxGridCustomView, cxGridCustomTableView, cxGridTableView, cxGridDBTableView,
  cxGrid, dxStatusBar, cxSplitter, ExtCtrls, dxBar, FIBDataSet, pFIBDataSet,
  FIBDatabase, pFIBDatabase, cxCheckListBox, dxSkinsForm, Buttons, Menus,
  JvFormPlacement, JvComponentBase, JvAppStorage, JvAppIniStorage,
  MemTableDataEh, ImgList, MemTableEh, cxProgressBar, cxGridBandedTableView,
  cxGridDBBandedTableView, cxLocalization, cxGridCustomPopupMenu,
  cxGridPopupMenu, JvDialogs, DModule, dxSkinSeven, dxSkinsdxRibbonPainter,
  dxRibbon, dxSkinMcSkin, dxmdaset, cxEditRepositoryItems, cxPropertiesStore,
  dxSkinOffice2010Black, dxSkinOffice2010Blue, dxSkinOffice2010Silver, cxButtons,
  DBGridEhGrouping;

type
  TNewPrincipForm = class(Tdxcustomribbonform)
    bar1: TdxBarManager;
    bar: TdxBar;
    pnl1: TPanel;
    pnl2: TPanel;
    Splitter: TcxSplitter;
    stat1: TdxStatusBar;
    l1: TcxGridLevel;
    grd1: TcxGrid;
    xpbr1: TJvXPBar;
    pnl3: TPanel;
    grp1: TGroupBox;
    IsReestr: TCheckBox;
    dt1: TcxDateEdit;
    dt2: TcxDateEdit;
    lbl2: TcxLabel;
    xpbr2: TJvXPBar;
    SFGrid: TDBGridEh;
    tlb1: TToolBar;
    btnCheckAllBtn: TToolButton;
    btnUnCheckAll: TToolButton;
    btnSaveSrvBtn: TToolButton;
    btnOpenSrvBtn: TToolButton;
    btn_exit: TdxBarButton;
    btn_exec: TdxBarButton;
    trn1: TpFIBTransaction;
    DataSet1: TpFIBDataSet;
    ds1: TDataSource;
    DS_Kontragent: TpFIBDataSet;
    ds2: TDataSource;
    DS_Reestr: TpFIBDataSet;
    ds3: TDataSource;
    grp2: TGroupBox;
    cbb1: TcxLookupComboBox;
    grp3: TGroupBox;
    lst1: TcxCheckListBox;
    btn_clear: TSpeedButton;
    pm1: TPopupMenu;
    N1: TMenuItem;
    N2: TMenuItem;
    N3: TMenuItem;
    SFSource: TDataSource;
    SFTable: TMemTableEh;
    il1: TImageList;
    T1: TMemTableEh;
    TSource: TDataSource;
    DS: TpFIBDataSet;
    v2: TcxGridDBBandedTableView;
    cxlclzr1: TcxLocalizer;
    pmn1: TcxGridPopupMenu;
    pm2: TPopupMenu;
    Excel1: TMenuItem;
    Excel2: TMenuItem;
    N4: TMenuItem;
    dlg1: TJvSaveDialog;
    RibbonTab1: TdxRibbonTab;
    Ribbon: TdxRibbon;
    lbl1: TcxLabel;
    lbl3: TcxLabel;
    Mem: TMemTableEh;
    cxstylrpstry1: TcxStyleRepository;
    cxstyl1: TcxStyle;
    EditRepository1: TcxEditRepository;
    CurrencyItem1: TcxEditRepositoryCurrencyItem;
    cbb_utsk: TcxLookupComboBox;
    btn_utsk_clear: TSpeedButton;
    DS_UTSK: TpFIBDataSet;
    ds4: TDataSource;
    prpstor1: TcxPropertiesStore;
    btn_excel: TcxButton;
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure btn_exitClick(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure cbb1PropertiesChange(Sender: TObject);
    procedure btn_clearClick(Sender: TObject);
    procedure N1Click(Sender: TObject);
    procedure N2Click(Sender: TObject);
    procedure btnCheckAllBtnClick(Sender: TObject);
    procedure btnUnCheckAllClick(Sender: TObject);
    procedure btnSaveSrvBtnClick(Sender: TObject);
    procedure btnOpenSrvBtnClick(Sender: TObject);
    procedure SFGridDblClick(Sender: TObject);
    procedure SFGridKeyDown(Sender: TObject; var Key: Word; Shift: TShiftState);
    procedure btn_execClick(Sender: TObject);
    procedure btn2Click(Sender: TObject);
    procedure Excel1Click(Sender: TObject);
    procedure Excel2Click(Sender: TObject);
    procedure btn1Click(Sender: TObject);
    procedure btn_utsk_clearClick(Sender: TObject);
    procedure FormActivate(Sender: TObject);
    procedure btn_excelClick(Sender: TObject);
  private
    { Private declarations }
    StartTime :TTime;
    UTSK :string;
    procedure GetReestr(kontr :string);
    procedure SrvMark(AMark: boolean);
    procedure SrvListSave;
    function GetSrvList: string;
    procedure SrvListLoad;
    procedure SetSrvList(S: string);
    procedure MakeReport;
    function GetReestrCode :string;
    function GetReestrByKontragent(kontragent :string) :string;
    procedure SetMsg(Num: integer; Msg: string);
    procedure CreateT1(YM1,YM2: string; TGRK: boolean = false);
    procedure ClearRecord(YM1,YM2: string);
    procedure CreateGrid(YM1, YM2: string);
    procedure CreateMemTable;
    procedure UtscClear;
  public
    { Public declarations }

  end;

var
  NewPrincipForm: TNewPrincipForm;
implementation

uses SrvFltUnit, NameFrm, SrvSaveListFrm, cmUtils;

{$R *.dfm}

function GetMonthCaption(YM: string): string;
const M: array[1..12] of string[5] =
         ('янв.','фев.','март','апр.','май','июнь','июль','авг.','сент.','окт.','нояб.','дек.');
 var i: integer;
begin
 i:=StrToInt(Copy(YM,3,2));
 Result:=string(M[i])+' '+Copy(YM,1,2);
end;

function GetYM(M,Y: integer): string;
begin
 Result:=IntToStr(M);
 if Length(Result)=1 then Result:='0'+Result;
 Result:=Copy(IntToStr(Y),3,2)+Result;
end;

function NextYM(YM: string): string;
 var M,Y: integer;
begin
 M:=StrToInt(Copy(YM,3,2));
 Y:=2000+StrToInt(Copy(YM,1,2));
 Inc(M);
 if M=13 then
  begin
   Inc(Y); M:=1;
  end;
 Result:=GetYM(M,Y);
end;

procedure TNewPrincipForm.btn1Click(Sender: TObject);
begin
 CreateMemTable;
end;

procedure TNewPrincipForm.btn2Click(Sender: TObject);
begin
  v2.ClearItems;
  v2.Bands.Clear;
end;

procedure TNewPrincipForm.btnCheckAllBtnClick(Sender: TObject);
begin
 SrvMark(True);
end;

procedure TNewPrincipForm.btnOpenSrvBtnClick(Sender: TObject);
begin
  SrvListLoad;
end;

procedure TNewPrincipForm.btnSaveSrvBtnClick(Sender: TObject);
begin
 SrvListSave;
end;

procedure TNewPrincipForm.btnUnCheckAllClick(Sender: TObject);
begin
 SrvMark(false);
end;

procedure TNewPrincipForm.btn_clearClick(Sender: TObject);
begin
 cbb1.Clear;
 cbb1.Properties.Grid.DataController.Values
  [cbb1.Properties.Grid.FocusedRowIndex,0]:='';
 GetReestr('');
 DS_Kontragent.CloseOpen(false);
end;

procedure TNewPrincipForm.btn_excelClick(Sender: TObject);
begin
 if dlg1.Execute then
 ExportGridToExcel(dlg1.FileName,grd1,true,True,True,'xls');
end;
//Выполняем запрос
procedure TNewPrincipForm.btn_execClick(Sender: TObject);
var
  day :Integer;
begin
 day:= DaysBetween(dt1.Date, dt2.Date);
 if day>60 then
 begin
  if MessageDlg('Вы действительно хотите сформировать отчет за' + #13#10 +
                '                '+DateName(day)+
                ' ?',  mtWarning, [mbYes, mbNo], 0) = mrYes then
  begin
     MakeReport;
  end else Exit;
 end else MakeReport;
end;

procedure TNewPrincipForm.ClearRecord(YM1,YM2: string);
begin
   repeat
    T1.FieldByName('P'+YM1).AsFloat:=0;
    T1.FieldByName('R'+YM1).AsFloat:=0;
    T1.FieldByName('U'+YM1).AsFloat:=0;
    T1.FieldByName('D'+YM1).AsFloat:=0;
    T1.FieldByName('M'+YM1).AsFloat:=0;
    YM1:=NextYM(YM1); //к следующему месяцу
   until YM1>YM2;
end;
{------------------------------------------------------------------------------
TNewPrincipForm.CreateGrid:  Создаем грид
_______________________________________________________________________________}
procedure TNewPrincipForm.CreateGrid(YM1, YM2: string);
var
  C :TcxGridDBBandedColumn;
  i :Integer;
begin
        v2.ClearItems;
        v2.Bands.Clear;
      i:=0;
        v2.Bands.Add;
        v2.Bands[i].Caption := ' ';
        v2.Bands[i].FixedKind:=fkLeft;
        v2.Bands[i].Options.HoldOwnColumnsOnly:=True;

      C := v2.CreateColumn;
      C.DataBinding.FieldName := 'IsReestr';
      C.Caption:=' ';
      C.Name := 'cl'+'IsReestr';
      c.Width:=20;
      C.Position.BandIndex := i;

      C := v2.CreateColumn;
      C.DataBinding.FieldName := 'AccCode';
      C.Name := 'cl'+'AccCode';
      C.Caption:='Счет';
      C.Position.BandIndex := i;
      C.Summary.FooterKind:=skCount;
        v2.Bands.Add;
        Inc(i);
        v2.Bands[i].Caption := 'Лицевой счет';
        v2.Bands[i].Options.HoldOwnColumnsOnly:=True;
      C := v2.CreateColumn;
      C.DataBinding.FieldName := 'Master';
      C.Name := 'cl'+'Master';
      C.Caption:='Владелец';
      C.Position.BandIndex := i;

      C := v2.CreateColumn;
      C.DataBinding.FieldName := 'Kontragent';
      C.Name := 'cl'+'Kontragent';
      C.Caption:='Принципал';
      C.Position.BandIndex := i;
      if IsReestr.Checked then
      begin
        C := v2.CreateColumn;
        C.DataBinding.FieldName := 'ReestrSum';
        C.Name := 'cl'+'ReestrSum';
        C.Caption:='Сумма';
        C.Position.BandIndex := i;
        C.Summary.FooterKind:=skSum;
        C.Summary.FooterFormat:= ',0.00';
        C.RepositoryItem:=CurrencyItem1;

        C := v2.CreateColumn;
        C.DataBinding.FieldName := 'Reestr';
        C.Name := 'cl'+'Reestr';
        C.Caption:='Реестр';
        C.Position.BandIndex := i;
      end;
        v2.Bands.Add;
        Inc(i);
        v2.Bands[i].Caption := 'Адрес';
        v2.Bands[i].Options.HoldOwnColumnsOnly:=True;
      C := v2.CreateColumn;
      C.DataBinding.FieldName := 'Street';
      C.Name := 'cl'+'Street';
      C.Caption:='Улица';
      C.Position.BandIndex := i;

      C := v2.CreateColumn;
      C.DataBinding.FieldName := 'House';
      C.Name := 'cl'+'House';
      C.Caption:='Дом';
      C.Position.BandIndex := i;

      C := v2.CreateColumn;
      C.DataBinding.FieldName := 'Flat';
      C.Name := 'cl'+'Flat';
      C.Caption:='Кв.';
      C.Position.BandIndex := i;

      C := v2.CreateColumn;
      C.DataBinding.FieldName := 'Ost';
      C.Name := 'cl'+'Ost';
      C.Caption:='Нач.остаток';
      C.Position.BandIndex := i;
      C.Summary.FooterKind:=skSum;
      C.Summary.FooterFormat:= ',0.00';
      C.RepositoryItem:=CurrencyItem1;

      repeat
          v2.Bands.Add;
          Inc(i);
          v2.Bands[i].Caption := GetMonthCaption(YM1);
          v2.Bands[i].Options.HoldOwnColumnsOnly:=True;
        C := v2.CreateColumn;
        C.DataBinding.FieldName := 'P'+YM1;
        C.Name := 'cl'+'P'+YM1;
        C.Caption:='Начисл.';
        C.Position.BandIndex := i;
        C.Summary.FooterKind:=skSum;
        C.Summary.FooterFormat:= ',0.00';
        C.RepositoryItem:=CurrencyItem1;

        C := v2.CreateColumn;
        C.DataBinding.FieldName := 'R'+YM1;
        C.Name := 'cl'+'R'+YM1;
        C.Caption:='Оплаты';
        C.Position.BandIndex := i;
        C.Summary.FooterKind:=skSum;
        C.Summary.FooterFormat:= ',0.00';
        C.RepositoryItem:=CurrencyItem1;

        C := v2.CreateColumn;
        C.DataBinding.FieldName := 'U'+YM1;
        C.Name := 'cl'+'U'+YM1;
        C.Caption:='Долг +';
        C.Position.BandIndex := i;
        C.Summary.FooterKind:=skSum;
        C.Summary.FooterFormat:= ',0.00';
        C.RepositoryItem:=CurrencyItem1;

        C := v2.CreateColumn;
        C.DataBinding.FieldName := 'D'+YM1;
        C.Name := 'cl'+'D'+YM1;
        C.Caption:='Долг -';
        C.Position.BandIndex := i;
        C.Summary.FooterKind:=skSum;
        C.Summary.FooterFormat:= ',0.00';
        C.RepositoryItem:=CurrencyItem1;

        C := v2.CreateColumn;
        C.DataBinding.FieldName := 'M'+YM1;
        C.Name := 'cl'+'M'+YM1;
        C.Caption:='Откл.';
        C.Position.BandIndex := i;
        C.Summary.FooterKind:=skSum;
        C.Summary.FooterFormat:= ',0.00';
        C.RepositoryItem:=CurrencyItem1;

        YM1:=NextYM(YM1); //к следующему месяцу
      until YM1>YM2;

        v2.Bands.Add;
        inc(i);
        v2.Bands[i].Caption := 'Итого';
        v2.Bands[i].Options.Moving:=False; //не перемещать
        v2.Bands[i].Options.HoldOwnColumnsOnly:=True;
      C := v2.CreateColumn;
      C.DataBinding.FieldName := 'PSum';
      C.Name := 'cl'+'PSum';
      C.Caption:='Начисл.';
      C.Position.BandIndex := i;
      C.Summary.FooterKind:=skSum;
      C.Summary.FooterFormat:= ',0.00';
      C.RepositoryItem:=CurrencyItem1;

      C := v2.CreateColumn;
      C.DataBinding.FieldName := 'RSum';
      C.Name := 'cl'+'RSum';
      C.Caption:='Оплачено';
      C.Position.BandIndex := i;
      C.Summary.FooterKind:=skSum;
      C.Summary.FooterFormat:= ',0.00';
      C.RepositoryItem:=CurrencyItem1;

      C := v2.CreateColumn;
      C.DataBinding.FieldName := 'U';
      C.Name := 'cl'+'U';
      C.Caption:='Долг +';
      C.Position.BandIndex := i;
      C.Summary.FooterKind:=skSum;
      C.Summary.FooterFormat:= ',0.00';
      C.RepositoryItem:=CurrencyItem1;

      C := v2.CreateColumn;
      C.DataBinding.FieldName := 'D';
      C.Name := 'cl'+'D';
      C.Caption:='Долг -';
      C.Position.BandIndex := i;
      C.Summary.FooterKind:=skSum;
      C.Summary.FooterFormat:= ',0.00';
      C.RepositoryItem:=CurrencyItem1;

      C := v2.CreateColumn;
      C.DataBinding.FieldName := 'M';
      C.Name := 'cl'+'M';
      C.Caption:='Откл.';
      C.Position.BandIndex := i;
      C.Summary.FooterKind:=skSum;
      C.Summary.FooterFormat:= ',0.00';
      C.RepositoryItem:=CurrencyItem1;

        v2.Bands.Add;
        Inc(i);
        v2.Bands[i].Caption := '';
        v2.Bands[i].Options.HoldOwnColumnsOnly:=True;
      C := v2.CreateColumn;
      C.DataBinding.FieldName := 'Saldo';
      C.Name := 'cl'+'Saldo';
      C.Caption:='Кон.ост';
      C.Position.BandIndex := i;
      C.Summary.FooterKind:=skSum;
      C.Summary.FooterFormat:= ',0.00';
      C.RepositoryItem:=CurrencyItem1;

end;

{------------------------------------------------------------------------------
 MakeReport:  Формируем отчет
_______________________________________________________________________________}
procedure TNewPrincipForm.MakeReport;
var
  Q :TFIBQuery;
  sql,             //запрос
  kontr,           //код контрагента
  rcode,s,YM1,YM2 :string; //список кодов отмеченных реестров через запятую
  i :Integer;
  ls,RList :TStringList;
  StartTime :TTime;
  RSum,PSum: currency;

 procedure CalcOtkl(PSum,RSum: currency; var U,D,M: currency);
  begin
   U:=0; D:=0;
   if PSum<0 then PSum:=0;
   if RSum<0 then RSum:=0;
   M:=PSum-RSum;
   if M>0 then U:=M else D:=-M;
  end;

 procedure CalcItog;
  var M,D,U,Ost: currency;
      Y: string;
  begin
   if not (T1.State in [dsEdit, dsInsert]) then Exit;
   T1.FieldByName('PSum').AsCurrency:=PSum;
   T1.FieldByName('RSum').AsCurrency:=RSum;
   Ost:=T1.FieldByName('Ost').AsCurrency;
   T1.FieldByName('Saldo').AsCurrency:=Ost+
                                       T1.FieldByName('PSum').AsCurrency-
                                       T1.FieldByName('RSum').AsCurrency;
   //расчет отклонений по месяцам
   Y:=YM1;
   repeat
    CalcOtkl(T1.FieldByName('P'+Y).AsCurrency,
             T1.FieldByName('R'+Y).AsCurrency,
             U, D, M);
    T1.FieldByName('U'+Y).AsCurrency:=U;
    T1.FieldByName('D'+Y).AsCurrency:=D;
    T1.FieldByName('M'+Y).AsCurrency:=M;
    Y:=NextYM(Y); //к следующему месяцу
   until Y>YM2;
   //расчет итоговых отклонений
   CalcOtkl(PSum,RSum,U,D,M);
   T1.FieldByName('U').AsCurrency:=U;
   T1.FieldByName('D').AsCurrency:=D;
   T1.FieldByName('M').AsCurrency:=M;
   //сохраняем запись
   T1.Post;
   PSum:=0; RSum:=0;
  end;

 procedure AddT1Record(RList: TStrings);
  var FName, S: string;
  begin
   //вносим запись в таблицу группировки данных
   if (T1.FieldByName('AccCode').AsString<>DS.FN('AccCode').AsString) then
     begin
      if not T1.IsEmpty then CalcItog;
      T1.Append;
      T1.FieldByName('IsReestr').AsInteger:=DS.FN('IsReestr').AsInteger;
      T1.FieldByName('AccCode').AsString:=DS.FN('AccCode').AsString;
      T1.FieldByName('Master').AsString:=Copy(DS.FN('Master').AsString,1,50);
      T1.FieldByName('Kontragent').AsString:=Copy(DS.FN('KontragentName').AsString,1,50);
      T1.FieldByName('Street').AsString:=DS.FN('Street').AsString;
      T1.FieldByName('House').AsString:=DS.FN('House').AsString;
      T1.FieldByName('Flat').AsString:=DS.FN('Flat').AsString;
      //поля реестра ТГРК
      if Mem.Locate('AcCode',DS.FN('AccCode').AsString,[]) then
//      S:=RList.Values[DS.FN('AccCode').AsString];
//      if S<>'' then
       begin
        //T1.FieldByName('TGRK').AsString:=GetWord(S,1,';');
        //T1.FieldByName('YM').AsString:=GetWord(S,2,';');
        T1.FieldByName('ReestrSum').AsFloat:=Mem.FieldByName('Summa').AsFloat;
        T1.FieldByName('Reestr').AsInteger:=Mem.FieldByName('Reestr').AsInteger;
        //T1.FieldByName('ReestrSum').AsString:=GetWord(S,1,';');
        //T1.FieldByName('Reestr').AsString:=GetWord(S,2,';');
       end;
      ClearRecord(YM1,YM2);
     end;
   //остаток на начало периода
   if DS.FN('YM').AsString=YM1 then
     T1.FieldByName('Ost').AsCurrency:=(T1.FieldByName('Ost').AsCurrency+
                                                     DS.FN('Summa').AsCurrency);
   //суммируем начисления
   FName:='P'+DS.FN('YM').AsString;
   PSum:=PSum+DS.FN('PSum').AsCurrency;
   T1.FieldByName(FName).AsCurrency:=T1.FieldByName(FName).AsCurrency+
                                                       DS.FN('PSum').AsCurrency;
   //суммируем оплаты
   FName:='R'+DS.FN('YM').AsString;
   RSum:=RSum+DS.FN('RSum').AsCurrency;
   T1.FieldByName(FName).AsCurrency:=T1.FieldByName(FName).AsCurrency+
                                                       DS.FN('RSum').AsCurrency;
  end;

 procedure ExData(S:string; RList: TStrings);
  var Num: integer;
      S1: string;
 begin
   S1:=GetSrvList;
   S1:='pos('#39';'#39' || a.Srv || '#39';'#39', '#39+S1+#39')<>0';
     DS.Close;
   DS.SelectSQL.Text:=
    'select ACCCODE, SPR$SCHET.MASTER MASTER, YM, SUMMA, PSUM, RSUM,'+
    ' SPR$STREET.NAME STREET, SPR$ADDR.HOUSE HOUSE,'+
    ' SPR$ADDR.FLAT FLAT, SPR$KONTRAGENT.NAME KONTRAGENTNAME'+
    ' ,IIF(exists(select AccCode from Reestr where AccCode=a.AccCode),'+
    ' (select State from Reestr where AccCode=a.AccCode),-1) IsReestr'+
    ' from SALDO A'+
    ' left join SPR$SCHET on (SPR$SCHET.CODE = A.ACCCODE)'+
    ' left join SPR$KONTRAGENT on (SPR$SCHET.KONTRAGENT = SPR$KONTRAGENT.CODE)'+
    ' left join SPR$ADDR on (SPR$ADDR.CODE = SPR$SCHET.ADDR)'+
    ' left join SPR$STREET on (SPR$STREET.CODE = SPR$ADDR.STREET)'+
    ' where YM between '+#39+YM1+#39+' and '+#39+YM2+#39+' and '+S1+' and '+
    ' AccCode in ('+S+') order by AccCode';
    //порядок сортировки данных
    //ShowMessage(DS.ReadySelectText);
    //DS.SelectSQL.Text:=DS.SelectSQL.Text+' order by AccCode';
    DS.Open;
    Num:=0; RSum:=0; PSum:=0;
    while not DS.Eof do
     begin
      AddT1Record(RList);
      //stat1.Panels[0].Text:=TimeToStr(Time-StartTime);
      Application.ProcessMessages;
      DS.Next;
      Inc(Num);
      if Frac(Num/100)=0 then SetMsg(Num,' Заполняем таблицу');
     end;
  if not T1.IsEmpty then CalcItog;
 end;
 //получаем список контрагентов из отмеченных реестров
 function GetKontragent(rcode :string) :string;
 var
  Qk :TFIBQuery;
  sqlk,sk :string;
 begin
  if rcode<>'' then
  begin
    sqlk:='Select distinct(kontragent) from n_reestr where code in ('+rcode+')';
    Qk:=GetQueryForUse(trn1,sqlk);
    try
    Qk.ExecQuery;
    while not Qk.Eof do
    begin
     sk:=#39+Qk.FN('kontragent').AsString+#39+',';
     Qk.Next;
    end;
     if sk<>'' then Delete(sk,Length(sk),1);
     Result:=sk;
    finally

    end;
    FreeQueryForUse(Qk);
  end else
  Result:=#39+cbb1.EditValue+#39;

 end;

 procedure FillList(rcode :string);
  var
    t,n,num,sel :integer;
    kont, kontrClause,kontragent :string;
    //QReestr :TFIBQuery;
 begin
     if rcode='' then IsReestr.Checked:=False;

     sel:=cbb_utsk.Properties.Grid.FocusedRowIndex;
     kontragent:=VarToStr(cbb_utsk.Properties.Grid.DataController.Values[sel,0]);

    if IsReestr.Checked then
    begin
      if kontragent<>'' then //выбираем только контрагента утск
      sql:='select nr.code ,nr.accode, nr.summa,nr.reestr'+
           ' from n_reestrdata nr'+
           ' left outer join spr$schet sp on sp.code=nr.accode'+
           ' where reestr in ('+rcode+')'+
                   ' and sp.kontragent='+#39+kontragent+#39+
           ' order by accode'  //order by важен!!
      else
      sql:='select code ,accode, summa,reestr from n_reestrdata  where reestr in ('+
          rcode+') order by accode' //order by важен!!
    end  else
    begin
      kontr:=GetKontragent(rcode);
     // sql:='select Code accode from spr$schet where kontragent in ('+kontr+')';
      sql:='select distinct(Code) accode from spr$schet where kontragent in ('+kontr+')';
    end;
    Q:=GetQueryForUse(trn1,sql);
    //Q.ParamByName('rcode').AsString:=rcode;
    try
      Q.ExecQuery;
      Num:=0;
      while not Q.Eof do
      begin
       //если этот счет уже добавлен в таблицу
       if Mem.FieldByName('AcCode').AsString=Q.FN('accode').AsString then
       begin
         // и код счета меньше добавляемого (значит загружен раньше)
         if Mem.FieldByName('Code').AsInteger<Q.FN('code').AsInteger then
         begin
          // редактируем счет
           Mem.Edit;
           Mem.FieldByName('AcCode').AsString:=Q.FN('accode').AsString;
           //ls.Add(#39+Q.FN('accode').AsString+#39);
           Inc(Num);
           if IsReestr.Checked then
           begin
            Mem.FieldByName('reestr').AsInteger:=Q.FN('Reestr').AsInteger;
            Mem.FieldByName('Summa').AsFloat:=Q.FN('Summa').AsFloat;
           end;
           Mem.Post;
         end;
       end else
       begin //если не равен предыдущему то добавляем
           Mem.Append;
           Mem.FieldByName('AcCode').AsString:=Q.FN('accode').AsString;
           Inc(Num);
           if IsReestr.Checked then
           begin
           { QReestr:=GetQueryForUse(trn1,'select code from spr$schet where code=:acc');
            QReestr.ParamByName('acc').AsString:=Q.FN('accode').AsString;
            QReestr.ExecQuery;
            if QReestr.RecordCount>0 then}

            Mem.FieldByName('reestr').AsInteger:=Q.FN('Reestr').AsInteger;
            Mem.FieldByName('Summa').AsFloat:=Q.FN('Summa').AsFloat;
           end;
           Mem.Post;
       end;
       if Frac(Num/100)=0 then SetMsg(Num,'Собираем счета');
       Application.ProcessMessages;
       Q.Next;
      end;
    finally

    end;
    FreeQueryForUse(Q);
    Mem.First;
    n:=0; S:='';
    while not Mem.Eof  do
     begin
       inc(n);
       if S<>'' then S:=S+',';
       S:=S+#39+Mem.FieldByName('AcCode').AsString+#39;
       //Ls.Delete(0);
       if (n=1400) then
        begin
         ExData(S, RList);
         n:=0; S:='';
        end;
      //SetMsg(Ls.Count,'');
      Application.ProcessMessages;
      Mem.Next;
      //if Ls.Count=0 then Break; //покидаем цикл т.к. кончилось все
     end;
     //прогрузка последней партии счетов
     if S<>'' then ExData(S, RList);
     SetMsg(num,'Отчет закончен.');

 end;
 // получаем счета
 procedure GetAcCode;
 begin
    //если выбран контрагент
   i:=cbb1.Properties.Grid.FocusedRowIndex;
   kontr:=VarToStr(cbb1.Properties.Grid.DataController.Values[i,0]);
   if kontr<>'' then
   begin
     //если отмеченны реестры - получаем кода реестров
     rcode:=GetReestrCode;
     if rcode<>'' then
     begin
       //получаем список уникальных счетов по выбранным реестрам
       FillList(rcode);
     end else
     begin
       // получаем список уникальных счетов по выбранному контрагенту
      rcode:=GetReestrByKontragent(kontr);
      FillList(rcode);
     end;
   end else
   begin
     //если отмеченны реестры - получаем кода реестров
     rcode:=GetReestrCode;
     if rcode<>'' then
     begin
       //получаем список уникальных счетов по выбранным реестрам
       FillList(rcode);
     end
   end;
 end;
begin
 ls:=TStringList.Create;
 RList:=TStringList.Create;
  //формируем структуру таблицы в памяти
 YM1:=FormatDateTime('yy',dt1.Date)+FormatDateTime('mm',dt1.Date);
 YM2:=FormatDateTime('yy',dt2.Date)+FormatDateTime('mm',dt2.Date);
 CreateT1(YM1,YM2, true);
 StartTime:=Time;  //начало формирования отчета

 v2.DataController.DataSource:=nil;
 SetWaitCursor(True);
 CreateMemTable;
 GetAcCode;        //получаем уникальные счета
 CreateGrid(YM1,YM2);
 v2.DataController.DataSource:=TSource;
 SetWaitCursor;
 stat1.Panels[0].Text:=TimeToStr(Time-StartTime);
 v2.ApplyBestFit();
 RList.Free;
 ls.Free;

end;

procedure TNewPrincipForm.CreateMemTable;
var
  i :Integer;
begin
  if mem.FieldCount>0 then
  begin
    Mem.EmptyTable;
    Exit;
  end;
  with Mem.FieldDefs do
  begin
    Add('Code',ftInteger,0,True);
    Add('AcCode',ftString,32);
    Add('Reestr',ftInteger);
    Add('IsReestr',ftInteger);
    Add('Summa',ftFloat);
  end;
   Mem.IndexDefs.Add('IDX_AcCode','AcCode',[ixUnique]);
   Mem.CreateDataSet;
   Mem.Open;
end;
{------------------------------------------------------------------------------
GetReestrByKontragent:  Получаем кода реестров по коду контрагента
_______________________________________________________________________________}
function TNewPrincipForm.GetReestrByKontragent(kontragent :string) :string;
var
  i :Integer;
  Q :TFIBQuery;
  sql,s :string;
begin
  sql:='select nr.code from n_reestr nr where nr.kontragent=:kontragent order by nr.d1';
  Q:=GetQueryForUse(trn1,sql);
  try
   Q.ParamByName('kontragent').AsString:=kontragent;
   Q.ExecQuery;
   while not Q.Eof do
   begin
    s:=s+Q.FN('code').AsString+',';
    Q.Next;
   end;
  finally

  end;
  if s<>'' then Delete(s,Length(s),1);
  Result:=s;
  FreeQueryForUse(Q);
end;
{------------------------------------------------------------------------------
 GetReestrCode:Получаем кода выделенных реестров
_______________________________________________________________________________}
function TNewPrincipForm.GetReestrCode :string;
var
  i :Integer;
  s :string;
begin
  for i := 0 to Lst1.Count - 1 do
  begin
    if lst1.Items[i].Checked=true then
    s:=s+GetWord(lst1.Items[i].Text,3,'|')+',';
  end;
  if s<>'' then Delete(s,Length(s),1);
  Result:=s;
end;
{------------------------------------------------------------------------------}
procedure TNewPrincipForm.btn_exitClick(Sender: TObject);
begin
 Close;
end;

procedure TNewPrincipForm.btn_utsk_clearClick(Sender: TObject);
var
  kontragent :string;
begin
  UtscClear;
  { kontragent:=VarToStr(cbb_utsk.Properties.Grid.DataController.Values[
   cbb_utsk.Properties.Grid.FocusedRowIndex,0]);
   ShowMessage(kontragent);}
end;

procedure TNewPrincipForm.UtscClear;
begin
 cbb_utsk.Clear;
 cbb_utsk.Properties.Grid.DataController.Values
  [cbb_utsk.Properties.Grid.FocusedRowIndex,0]:='';
 DS_UTSK.CloseOpen(false);
end;

procedure TNewPrincipForm.cbb1PropertiesChange(Sender: TObject);
var
  sel :Integer;
  kontragent,reestr :string;
begin
 //при выборе контрагента получаем реестры
 sel:=cbb1.Properties.Grid.FocusedRowIndex;
 kontragent:=VarToStr(cbb1.Properties.Grid.DataController.Values[sel,0]);
 if kontragent='1-17' then
 begin
  cbb_utsk.Visible:=true;
  btn_utsk_clear.Visible:=True;
  reestr:=GetReestrByKontragent(kontragent);
  DS_UTSK.Close;
  DS_UTSK.SelectSQL.Text:=
  'select code,name from spr$kontragent where isUtsk=1';
   {' select DISTINCT(sp.kontragent) code,sk.name'+
   ' from N_REESTRDATA nr'+
   ' left outer join spr$schet sp on sp.code=nr.accode'+
   ' left outer join spr$kontragent sk on sk.code=sp.kontragent'+
   ' where nr.reestr in ('+reestr+')';}
  DS_UTSK.CloseOpen(False);
 end else
 begin
  if not DS_UTSK.IsEmpty then UtscClear;
   cbb_utsk.Visible:=False;
   btn_utsk_clear.Visible:=false;
 end;
 GetReestr(kontragent);
end;

procedure TNewPrincipForm.GetReestr(kontr :string);
var
  s,sql :string;
  Q :TFIBQuery;
begin
 sql:='select nr.code, nr.lasttime, nr.kontragent,sk.name, nr.note, nr.d1, nr.d2'+
      ' from n_reestr nr'+
      ' left outer join spr$kontragent sk on sk.code=nr.kontragent'+
      ' WHERE '+
      ' @@where_clause% 1 = 1 @';
  Q:=GetQueryForUse(trn1,sql);
  Q.ParamByName('where_clause').SetDefMacroValue;
 if kontr<>'' then
 begin
  Q.ParamByName('where_clause').AsString:='nr.kontragent=:Kontr';
  Q.ParamByName('Kontr').AsString:=kontr;
 end;
  Q.ExecQuery;
 lst1.Clear;
 while not Q.Eof do
 begin
  s:=
  FormatDateTime('dd.mm.yy',Q.FN('d1').AsDateTime)+' - '+
  FormatDateTime('dd.mm.yy',Q.FN('d2').AsDateTime)+' | '+
  Q.FN('Name').AsString+' | '+
  Q.FN('Code').AsString;
  with lst1.Items.Add do
  begin
    Text := s;
    //Enabled := False;
    State := cbsUnchecked;
  end;
  Q.Next;
 end;
end;


procedure TNewPrincipForm.CreateT1(YM1,YM2: string; TGRK: boolean = false);
var
  ADBColumn: TcxGridDBBandedColumn;
begin
 if YM1>YM2 then raise Exception.Create('Некорректно указан период формирования отчета.');
 T1.Close;
 with T1.FieldDefs do
  begin
   Clear;
   Add('IsReestr',ftInteger);
   Add('AccCode',ftString,12);
   Add('Kontragent',ftString,50);
   Add('Master',ftString,50);
   Add('Ost',ftFloat);
   Add('PSum', ftFloat);
   Add('RSum', ftFloat);
   Add('U', ftFloat);
   Add('D', ftFloat);
   Add('M', ftFloat);
   Add('Saldo', ftFloat);
   Add('Street',ftString,50);
   Add('House',ftString,10);
   Add('Flat',ftString,10);
   Add('ReestrSum', ftFloat);
   Add('Reestr', ftInteger);
   if TGRK then begin
     Add('TGRK', ftSmallint);
     Add('YM', ftString, 4);

   end;
   //добавляем список полей по месяцам
   repeat

    Add('P'+YM1, ftFloat); //столбец для оплат в течении месяца
    Add('R'+YM1, ftFloat); //столбец для оплат в течении месяца
    Add('U'+YM1, ftFloat); //увеличение задолженности
    Add('D'+YM1, ftFloat); //уменьшение задолженности
    Add('M'+YM1, ftFloat); //общее отклонение
    YM1:=NextYM(YM1); //к следующему месяцу
   until YM1>YM2;
  end;
 T1.CreateDataSet;
end;
{------------------------------------------------------------------------------
TNewPrincipForm.Excel1Click:  Экспорт в Excel
_______________________________________________________________________________}
procedure TNewPrincipForm.Excel1Click(Sender: TObject);
begin
 if dlg1.Execute then
 ExportGridToExcel(dlg1.FileName,grd1,false,true,True,'xls');
end;
{------------------------------------------------------------------------------
TNewPrincipForm.Excel2Click:  Развернуть и экспортировать в Excel
_______________________________________________________________________________}
procedure TNewPrincipForm.Excel2Click(Sender: TObject);
begin
 if dlg1.Execute then
 ExportGridToExcel(dlg1.FileName,grd1,true,True,True,'xls');
end;

{------------------------------------------------------------------------------
TNewPrincipForm.N1Click:  Выделить все
_______________________________________________________________________________}
procedure TNewPrincipForm.N1Click(Sender: TObject);
var
  i :integer;
begin
  for I := 0 to Lst1.Count - 1 do
  begin
    lst1.Items[i].Checked:=True;
  end;
end;

procedure TNewPrincipForm.N2Click(Sender: TObject);
var
  i :integer;
begin
  for I := 0 to Lst1.Count - 1 do
  begin
    lst1.Items[i].Checked:=false;
  end;
end;

procedure TNewPrincipForm.SrvMark(AMark: boolean);
begin
 SFSource.DataSet:=nil;
 with SFTable do
  begin
   First;
   while not Eof do
    begin
     if FieldByName('Flag').AsBoolean<>AMark then
      begin
       Edit;
       FieldByName('Flag').AsBoolean:=AMark;
       Post;
      end;
     Next;
    end;
   First;
  end;
 SFSource.DataSet:=SFTable;
end;

procedure TNewPrincipForm.SrvListSave;
 var Form: TNameForm;
begin
 Form:=TNameForm.Create(Application);
 Form.SrvList:=GetSrvList;
 Form.ShowModal;
 Form.Free;
end;

function TNewPrincipForm.GetSrvList: string;
begin
 Result:=';';
 SFSource.DataSet:=nil;
 with SFTable do
  begin
   First;
   while not Eof do
    begin
     if FieldByName('Flag').AsBoolean then
       Result:=Result+FieldByName('Srv').AsString+';';
     Next;
    end;
   First;
  end;
 SFSource.DataSet:=SFTable;
 if Result=';' then Result:='';
end;

procedure TNewPrincipForm.SrvListLoad;
 var Form: TSrvSaveListForm;
     S: string;
begin
 S:='';
 Form:=TSrvSaveListForm.Create(Application);
 if Form.ShowModal=mrOk then
    S:=Form.GetSrvList;
 Form.Free;
 if S<>'' then SetSrvList(S);
end;

procedure TNewPrincipForm.SetSrvList(S: string);
begin
 SFSource.DataSet:=nil;
 with SFTable do
  begin
   First;
   while not Eof do
     begin
      Edit;
      FieldByName('Flag').AsBoolean:=Pos(';'+FieldByName('Srv').AsString+';', S)<>0;
      Post;
      Next;
     end;
   First;
  end;
 SFSource.DataSet:=SFTable;
end;

procedure TNewPrincipForm.SFGridDblClick(Sender: TObject);
begin
 SFTable.Edit;
 SFTable.FieldByName('Flag').AsBoolean:=not SFTable.FieldByName('Flag').AsBoolean;
 SFTable.Post;
end;

procedure TNewPrincipForm.SFGridKeyDown(Sender: TObject; var Key: Word;
  Shift: TShiftState);
begin
 case Key of
  VK_SPACE: SFGridDblClick(nil);
 end;
end;

procedure TNewPrincipForm.FormActivate(Sender: TObject);
begin
  SetTabActive('rep',True);
end;

procedure TNewPrincipForm.FormClose(Sender: TObject; var Action: TCloseAction);
begin
 RemoveBtn(Name);
 Action:=caFree;
end;

procedure TNewPrincipForm.FormCreate(Sender: TObject);
begin
 AddButton(self);
 DS_Kontragent.Open;
 trn1.Active:=True;
  SFGrid.DataSource:=nil;
  InitSFTable(SFTable);
  SFGrid.DataSource:=SFSource;
  SFGrid.AutoFitColWidths:=true;
 if FileExists(ExtractFilePath(Application.ExeName)+'lang\rus.ini') then
 begin
  cxlclzr1.FileName:=ExtractFilePath(Application.ExeName)+'lang\rus.ini';
  cxlclzr1.Active:=True;
  cxlclzr1.Locale:=1049;
 end;
  prpstor1.StorageName:=ExtractFilePath(Application.ExeName)+'config\principal';
  if FileExists(ExtractFilePath(Application.ExeName)+'config\principal') then
  prpstor1.RestoreFrom;

{ if FileExists(ExtractFilePath(Application.ExeName)+'config\principal') then
 begin
  prpstor1.StorageName:=ExtractFilePath(Application.ExeName)+'config\principal';
  prpstor1.RestoreFrom;
 end;}
end;

procedure TNewPrincipForm.SetMsg(Num: integer; Msg: string);
begin
 with stat1 do
  begin
   //Panels[0].Text:=TimeToStr(Time-StartTime);
   if Msg<>'' then Panels[2].Text:=Msg;
   if Num=0 then Panels[1].Text:='' else Panels[1].Text:=IntToStr(Num);
   Application.ProcessMessages;
  end;
end;

end.
